﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;

namespace UDS.Build.Database
{
    public class SqlWriter
    {
        private TextWriter writer;

        public SqlWriter(TextWriter writer)
        {
            this.writer = writer;
        }

        private string GetParamType(SqlDbType dbType, int size)
        {
            string typeName = string.Empty;
            bool hasSize = false;
            switch (dbType)
            {
                case SqlDbType.BigInt:
                    typeName = "bigint";
                    break;
                case SqlDbType.Binary:
				    typeName = "binary";
                    hasSize = true;
                    break;
                case SqlDbType.Bit:
				    typeName = "bit";
                    break;
                case SqlDbType.Char:
				    typeName = "char";
                    hasSize = true;
                    break;
                case SqlDbType.Date:
				    typeName = "date";
                    break;
                case SqlDbType.DateTime:
				    typeName = "datetime";
                    break;
                case SqlDbType.DateTime2:
				    typeName = "datetime2";
                    break;
                case SqlDbType.DateTimeOffset:
				    typeName = "datetimeoffset";
                    break;
                case SqlDbType.Decimal:
				    typeName = "decimal";
                    hasSize = true;
                    break;
                case SqlDbType.Float:
				    typeName = "float";
                    break;
                case SqlDbType.Image:
				    typeName = "image";
                    break;
                case SqlDbType.Int:
				    typeName = "int";
                    break;
                case SqlDbType.Money:
				    typeName = "money";
                    break;
                case SqlDbType.NChar:
				    typeName = "nchar";
                    hasSize = true;
                    break;
                case SqlDbType.NText:
				    typeName = "ntext";
                    break;
                case SqlDbType.NVarChar:
				    typeName = "nvarchar";
                    hasSize = true;
                    break;
                case SqlDbType.Real:
				    typeName = "real";
                    break;
                case SqlDbType.SmallDateTime:
				    typeName = "smalldatetime";
                    break;
                case SqlDbType.SmallInt:
				    typeName = "smallint";
                    break;
                case SqlDbType.SmallMoney:
				    typeName = "smallmoney";
                    break;
                case SqlDbType.Structured:
				    typeName = "table";
                    break;
                case SqlDbType.Text:
				    typeName = "text";
                    break;
                case SqlDbType.Time:
				    typeName = "time";
                    break;
                case SqlDbType.Timestamp:
				    typeName = "timestamp";
                    break;
                case SqlDbType.TinyInt:
				    typeName = "tinyint";
                    break;
                case SqlDbType.Udt:
				    typeName = "binary";
                    break;
                case SqlDbType.UniqueIdentifier:
				    typeName = "uniqueidentifier";
                    break;
                case SqlDbType.VarBinary:
				    typeName = "varbinary";
                    hasSize = true;
                    break;
                case SqlDbType.VarChar:
				    typeName = "varchar";
                    hasSize = true;
                    break;
                case SqlDbType.Variant:
				    typeName = "sql_variant";
                    break;
                case SqlDbType.Xml:
				    typeName = "xml";
                    break;
            }
            if (hasSize)
            {
                return string.Format("{0}({1})", typeName, (size == -1)?"max":size.ToString());
            }
            return typeName;
        }

        public void WriteSprocDrop(string schemaName, string procName)
        {
            writer.WriteLine("IF ( OBJECT_ID(N'[{0}].[{1}]', 'P') IS NOT NULL )", schemaName, procName);
            writer.WriteLine("BEGIN");
            writer.WriteLine("\tDROP PROCEDURE [{0}].[{1}]", schemaName, procName);
            writer.WriteLine("END");
            writer.WriteLine("GO");
        }

        public void WriteSprocCreate(string schemaName, string procName, params SqlParameter[] parameters)
        {
            writer.WriteLine("\r\nCREATE PROCEDURE [{0}].[{1}]", schemaName, procName);
            if (parameters != null && parameters.Length > 0)
            {
                SqlParameter param;
                for (int i = 0; i < parameters.Length - 1; i++)
                {
                    param = parameters[i];
                    writer.WriteLine("{0} {1},", param.ParameterName, GetParamType(param.SqlDbType, param.Size));
                }
                param = parameters[parameters.Length - 1];
                writer.WriteLine("{0} {1}", param.ParameterName, GetParamType(param.SqlDbType, param.Size));
            }
            writer.WriteLine("AS\r\nBEGIN\r\n");
        }

        public void WriteSprocProlog(string schemaName, string procName, params SqlParameter[] parameters)
        {
            WriteSprocDrop(schemaName, procName);
            WriteSprocCreate(schemaName, procName, parameters);
        }


        public void WriteSprocEpilog()
        {
            writer.WriteLine("\r\nEND\r\nGO\r\n");
        }

        public void WriteText(string text)
        {
            writer.Write(text);
        }

        public void Close()
        {
            writer.Close();
        }

        public void WriteViewDrop(string schemaName, string viewName, bool includeGo)
        {
            writer.WriteLine("IF ( OBJECT_ID(N'[{0}].[{1}]', 'V') IS NOT NULL )", schemaName, viewName);
            writer.WriteLine("BEGIN");
            writer.WriteLine("\tDROP VIEW [{0}].[{1}]", schemaName, viewName);
            writer.WriteLine("END");
            if (includeGo)
            {
                writer.WriteLine("GO");
            }
        }
    }
}
